package com.swlz.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.swlz.commons.DBUtil;
import com.swlz.dto.UserSearch;
import com.swlz.entity.User;
import com.swlz.vo.UserVO;

import java.util.Date;

import cn.hutool.core.util.StrUtil;
/**
 * <pre>
 * 数据访问对象
 * 系统用户
 * TableName : SYS_USER
 * ClassName : com.swlz.dao.UserDAO
 * Author : swlz
 * E-Mail : 2233875735@qq.com
 * Date : 2020-06-24 04:36:23
 * Version : 1.0
 * Copyright 2020 swlz.top Inc. All rights reserved. 
 * Warning: this content is only for internal circulation of the company. It is forbidden to divulge it or use it for other commercial purposes
 * </pre>
 **/
public class UserDAO {

	/**
	 * 添加一个系统用户
	 * 包含所有字段
	 * @param user - 实体对象
	 * @return - 影响行数
	 * @throws SQLException
	 */
	public int insert(User user) throws SQLException {
		if(user.getUserId()==null) {
			user.setUserId(DBUtil.nextId());
		}
		user.setCreateTime(new Date());
		user.setVersionNum(0);
		user.setSortNumber(0L);
		return DBUtil.insert(user);
	}
 
	
	/**
	 * 添加一个系统用户
	 * 只包含非null的字段
	 * @param user - 实体对象
	 * @return - 影响行数
	 * @throws SQLException
	 */
	public int insertSelective(User user) throws SQLException {
		if(user.getUserId()==null) {
			user.setUserId(DBUtil.nextId());
		}
		user.setCreateTime(new Date());
		user.setVersionNum(0);
		user.setSortNumber(0L);
		return DBUtil.insertSelective(user);
	}
	

	/**
	 * 根据主键更新一个系统用户
	 * 包含所有字段
	 * @param user - 实体对象
	 * @return - 影响行数
	 * @throws SQLException
	 */
	public int update(User user) throws SQLException {
		user.setUpdateTime(new Date());
		return DBUtil.update(user);
	}
	
	/**
	 * 根据主键更新一个系统用户
	 * 只包含非null的字段
	 * @param user - 实体对象
	 * @return - 影响行数
	 * @throws SQLException
	 */
	public int updateSelective(User user) throws SQLException {
		user.setUpdateTime(new Date());
		return DBUtil.updateSelective(user);
	}
	
	
	
	/**
	 * 根据主键删除一个系统用户
	 * @param userId - 主键
	 * @return
	 * @throws SQLException
	 */
	public int deleteById(Long userId) throws SQLException  {
		return DBUtil.execute("DELETE FROM SYS_USER WHERE USER_ID=?", userId);
	}
	
	/**
	 * 根据主键获取一个系统用户
	 * @param userId - 主键
	 * @return
	 * @throws SQLException 没有获取到，或者获取多个都会抛出异常
	 */
	public User selectById(Long userId) throws SQLException  {
		return DBUtil.get("SELECT * FROM SYS_USER WHERE USER_ID=?", User.class ,userId);
	}

		
	/**
	 * 根据模型中的非空字段查询
	 * @param userId - 实体对象
	 * @return
	 * @throws SQLException 没有获取到，或者获取多个都会抛出异常
	 */
	public User selectByModel(User user)throws SQLException {
		StringBuffer sql = new StringBuffer("SELECT * FROM SYS_USER WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		where(user,sql,params);
		return DBUtil.get(sql.toString(), User.class, params.toArray());
	}
	
	/**
	 * 根据模型中的非空字段查询
	 * @param userId - 实体对象
	 * @param index - 开始索引
	 * @param len - 返回长度
	 * @return
	 * @throws SQLException
	 */
	public List<User> selectByModel(User user,int index,int len)throws SQLException {
		StringBuffer sql = new StringBuffer("SELECT * FROM SYS_USER WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		where(user,sql,params);
		sql.append(" ORDER BY USER_ID DESC LIMIT ?,?");
		params.add(index);
		params.add(len);
		return DBUtil.list(sql.toString(), User.class, params.toArray());
	}

	/**
	 * 根据模型中的非空字段统计
	 * @param userId - 实体对象
	 * @return
	 * @throws SQLException
	 */
	public long countByModel(User user)throws SQLException {
		StringBuffer sql = new StringBuffer("SELECT count(*) as c FROM SYS_USER WHERE 1=1 ");
		List<Object> params = new ArrayList<Object>();
		where(user,sql,params);
		return DBUtil.getLong(sql.toString(), params.toArray());
	}
	
	/**
	 * 格式化where条件和参数
	 * @param userId - 实体对象
	 * @param sql - SQL语句
	 * @param params - SQL语句中的参数
	 */
	private void where(User user,StringBuffer sql,List<Object> params) {
		if(user==null){
			return;
		}

		if(user.getUserId()!=null) {
			sql.append(" AND USER_ID=?");
			params.add(user.getUserId());
		}

		if(user.getDeptId()!=null) {
			sql.append(" AND DEPT_ID=?");
			params.add(user.getDeptId());
		}

		if(user.getRoleId()!=null) {
			sql.append(" AND ROLE_ID=?");
			params.add(user.getRoleId());
		}

		if(user.getUserName()!=null) {
			sql.append(" AND USER_NAME=?");
			params.add(user.getUserName());
		}

		if(user.getUserNickname()!=null) {
			sql.append(" AND USER_NICKNAME=?");
			params.add(user.getUserNickname());
		}

		if(user.getUserPassword()!=null) {
			sql.append(" AND USER_PASSWORD=?");
			params.add(user.getUserPassword());
		}

		if(user.getUserSalt()!=null) {
			sql.append(" AND USER_SALT=?");
			params.add(user.getUserSalt());
		}

		if(user.getUserPhone()!=null) {
			sql.append(" AND USER_PHONE=?");
			params.add(user.getUserPhone());
		}

		if(user.getUserEmail()!=null) {
			sql.append(" AND USER_EMAIL=?");
			params.add(user.getUserEmail());
		}

		if(user.getUserQq()!=null) {
			sql.append(" AND USER_QQ=?");
			params.add(user.getUserQq());
		}

		if(user.getUserAlipay()!=null) {
			sql.append(" AND USER_ALIPAY=?");
			params.add(user.getUserAlipay());
		}

		if(user.getUserWechat()!=null) {
			sql.append(" AND USER_WECHAT=?");
			params.add(user.getUserWechat());
		}

		if(user.getUserSex()!=null) {
			sql.append(" AND USER_SEX=?");
			params.add(user.getUserSex());
		}

		if(user.getUserBrithdate()!=null) {
			sql.append(" AND USER_BRITHDATE=?");
			params.add(user.getUserBrithdate());
		}

		if(user.getUserImg()!=null) {
			sql.append(" AND USER_IMG=?");
			params.add(user.getUserImg());
		}

		if(user.getUserAddressCode()!=null) {
			sql.append(" AND USER_ADDRESS_CODE=?");
			params.add(user.getUserAddressCode());
		}

		if(user.getUserAddress()!=null) {
			sql.append(" AND USER_ADDRESS=?");
			params.add(user.getUserAddress());
		}

		if(user.getUserMemo()!=null) {
			sql.append(" AND USER_MEMO=?");
			params.add(user.getUserMemo());
		}

		if(user.getUserScore()!=null) {
			sql.append(" AND USER_SCORE=?");
			params.add(user.getUserScore());
		}

		if(user.getUserLevel()!=null) {
			sql.append(" AND USER_LEVEL=?");
			params.add(user.getUserLevel());
		}

		if(user.getUserStatus()!=null) {
			sql.append(" AND USER_STATUS=?");
			params.add(user.getUserStatus());
		}

		if(user.getDeleted()!=null) {
			sql.append(" AND DELETED=?");
			params.add(user.getDeleted());
		}

		if(user.getDeleteUser()!=null) {
			sql.append(" AND DELETE_USER=?");
			params.add(user.getDeleteUser());
		}

		if(user.getDeleteTime()!=null) {
			sql.append(" AND DELETE_TIME=?");
			params.add(user.getDeleteTime());
		}

		if(user.getCreateUser()!=null) {
			sql.append(" AND CREATE_USER=?");
			params.add(user.getCreateUser());
		}

		if(user.getCreateTime()!=null) {
			sql.append(" AND CREATE_TIME=?");
			params.add(user.getCreateTime());
		}

		if(user.getVersionNum()!=null) {
			sql.append(" AND VERSION_NUM=?");
			params.add(user.getVersionNum());
		}

		if(user.getUpdateUser()!=null) {
			sql.append(" AND UPDATE_USER=?");
			params.add(user.getUpdateUser());
		}

		if(user.getUpdateTime()!=null) {
			sql.append(" AND UPDATE_TIME=?");
			params.add(user.getUpdateTime());
		}

		if(user.getSortNumber()!=null) {
			sql.append(" AND SORT_NUMBER=?");
			params.add(user.getSortNumber());
		}

	}
	
	
	public List<UserVO> select(UserSearch search,int index,int len)throws SQLException {
		StringBuffer sql = new StringBuffer("SELECT a.*,b.ROLE_NAME FROM sys_user a INNER JOIN sys_role b ON a.ROLE_ID = b.ROLE_ID");
		List<Object> params = new ArrayList<Object>();
		where(search,sql,params);
		sql.append(" ORDER BY "+search.getSortName()+" "+search.getSortOrder()+" LIMIT ?,?");
		params.add(index);
		params.add(len);
		System.out.println("SQL:"+sql);//日志
		System.out.println("patams:"+params);
		return DBUtil.list(sql.toString(), UserVO.class, params.toArray());
	}
	
	public long count(UserSearch search)throws SQLException {
		StringBuffer sql = new StringBuffer("SELECT count(*) as c FROM sys_user a INNER JOIN sys_role b ON a.ROLE_ID = b.ROLE_ID");
		List<Object> params = new ArrayList<Object>();
		where(search,sql,params);
		System.out.println("SQL:"+sql);//日志
		System.out.println("patams:"+params);
		return DBUtil.getLong(sql.toString(), params.toArray());
	}
	
	private void where(UserSearch search,StringBuffer sql,List<Object> params)throws SQLException {
		if(search.getRoleId()!=null){
			sql.append(" AND a.ROLE_ID=?");
			params.add(search.getRoleId());
		}
		if(!StrUtil.hasBlank(search.getKey())){
			sql.append(" and (a.USER_NAME like CONCAT('%',?,'%') or a.USER_NICKNAME like CONCAT('%',?,'%'))");
			params.add(search.getKey());
			params.add(search.getKey());
		}
	}
}